package Database;

import idp.MainController;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.sql.Timestamp;
import java.util.ArrayList;
import java.util.logging.Level;

import Communication.BoatDataWrapper;
import debuggen.Debug;

public class Database {

	//TESTDATA REMOVE..
	BoatDataWrapper data;	
	
	MainController mController;

	private int currentSessionID = -1;	

	public Database(MainController mController) throws ClassNotFoundException
	{
		this.mController = mController;
		Class.forName("org.sqlite.JDBC");
		//this.deleteTables();
		this.insertTables();
		this.createSession();
		this.processSessionIds();
	}

	/**
	 * Number of seconds since 1-1-1970..
	 * @return seconds
	 */
	private long createTimeStamp()
	{
		Timestamp time = new Timestamp(System.currentTimeMillis());
		return time.getTime();
	}

	/**
	 * Check if tables are created, else create them.
	 */
	private void insertTables()
	{
		//initialize connection
		Connection connection = initializeConnection();
		try
		{	      
			//create statement
			Statement statement = connection.createStatement();
			//set query timeout
			statement.setQueryTimeout(30);
			//create session table
			statement.executeUpdate("CREATE TABLE IF NOT EXISTS session (sessionID INTEGER PRIMARY KEY AUTOINCREMENT, datetime REAL)");
			//create data table
			statement.executeUpdate("CREATE TABLE IF NOT EXISTS data (dataID INTEGER PRIMARY KEY AUTOINCREMENT, " +
					"sessionID INTEGER, " +
					"angle INTEGER, " +
					"sailPosition INTEGER, " +
					"rudderPosition INTEGER, " + 
					"windDirection INTEGER, " + 
					"motor INTEGER, " + 
					"keel INTEGER, " + 
					"controlMethod INTEGER, " + 
					"RFID INTEGER, " + 
					"speed INTEGER, " + 
			"datetime REAL)");	      
		}
		catch(SQLException e)
		{
			if (Debug.DEBUGGEN)
			{
				Debug.writeMessage("Error in Database.insertTables", Level.WARNING, e);
			}
		}
		finally
		{
			try
			{
				if(connection != null)
					connection.close();
			}
			catch(SQLException e)
			{
				if (Debug.DEBUGGEN)
				{
					Debug.writeMessage("Error in Database.insertTables finally", Level.WARNING, e);
				}
			}
		}
	}
	
	/**
	 * Function deletes the tables session and data
	 */
	public void deleteTables()
	{
		//initialize connection
		Connection connection = initializeConnection();
		try
		{	    
			//create statement
			Statement statement = connection.createStatement();
			//set query timeout
			statement.setQueryTimeout(30);
			//drop session table
			statement.executeUpdate("DROP TABLE IF EXISTS session");
			//drop data table
			statement.executeUpdate("DROP TABLE IF EXISTS data");	           
		}
		catch(SQLException e)
		{
			if (Debug.DEBUGGEN)
			{
				Debug.writeMessage("Error in Database.deleteTables", Level.WARNING, e);
			}
		}
		finally
		{
			try
			{
				currentSessionID = -1;
				this.processSessionIds();
				if(connection != null)
					connection.close();
			}
			catch(SQLException e)
			{
				if (Debug.DEBUGGEN)
				{
					Debug.writeMessage("Error in Database.deleteTables FINALLY", Level.WARNING, e);
				}
			}
		}
	}

	/**
	 * Create new session / race. 
	 * A new record is added to the session table and id will be saved in currentSession var.
	 */
	public void createSession()
	{
		//Initialize connection
		Connection connection = initializeConnection();
		try
		{	      
			//Create statement
			Statement statement = connection.createStatement();
			//Set timeout
			statement.setQueryTimeout(30);  // set timeout to 30 sec.
			//Insert new session data in database
			statement.executeUpdate("INSERT  INTO session (datetime) VALUES (" + createTimeStamp() + ")");
			//Get new session data id.
			ResultSet res = statement.executeQuery("SELECT seq FROM SQLITE_SEQUENCE WHERE name = 'session'");
			//Store the new session id locally.
			currentSessionID = Integer.parseInt(res.getString("Seq"));	      
		}
		catch(SQLException e)
		{
			if (Debug.DEBUGGEN)
			{
				Debug.writeMessage("Error in Database.createSession", Level.WARNING, e);
			}
		}
		finally
		{
			try
			{				
				if(connection != null)
					connection.close();
				this.processSessionIds();
			}
			catch(SQLException e)
			{
				if (Debug.DEBUGGEN)
				{
					Debug.writeMessage("Error in Database.createSession FINALLY", Level.WARNING, e);
				}
			}
		}

	}	

	/**
	 * Initializing database connection.
	 * @return the actual connection
	 * @throws SQLException 
	 */
	private Connection initializeConnection() 
	{
		//create connection
		Connection connection = null;
		try 
		{
			//try to connect to database
			connection = DriverManager.getConnection("jdbc:sqlite:idpDatabase.db");	
		}
		catch(SQLException e)
		{
			if (Debug.DEBUGGEN)
			{
				Debug.writeMessage("Error in connection database.initializeConnection", Level.WARNING, e);
			}
		}		    
		return connection;
	}

	/**
	 * Insert boatdata with timestamp into session.
	 * @param BoatData
	 */
	public void insertDataInSession(BoatDataWrapper BoatData)
	{
		//Connect to database
		Connection connection = initializeConnection();
		//check if session is already created..
		if (currentSessionID == -1)
			createSession();
		try
		{
			//create statement
			Statement statement = connection.createStatement();
			//set timeout
			statement.setQueryTimeout(5);
			//insert data in session
			statement.executeUpdate("INSERT INTO data (sessionID, angle, sailPosition, rudderPosition, windDirection, motor, keel, controlMethod, RFID, speed, datetime) " +
					"VALUES ('" 
					+ currentSessionID + "', '"
					+ BoatData.getAngle() + "', '"
					+ BoatData.getSailPosition() + "', '"
					+ BoatData.getRudderPosition() + "', '"
					+ BoatData.getWindDirection() + "', '"
					+ BoatData.getMotor() + "',' "
					+ BoatData.getKeel() + "', '"
					+ BoatData.getControlMethod() + "', '"
					+ BoatData.getRFID() + "', '"
					+ BoatData.getSpeed() + "', '"											
					+ createTimeStamp() + "')");	
		}
		catch(SQLException e)
		{
			if (Debug.DEBUGGEN)
			{
				Debug.writeMessage("Error in database.insertDataInSession", Level.WARNING, e);
			}
		}
		finally
		{
			try
			{
				if(connection != null)
					connection.close();
			}
			catch(SQLException e)
			{
				if (Debug.DEBUGGEN)
				{
					Debug.writeMessage("Error in connection database.initializeConnection FINALLY", Level.WARNING, e);
				}
			}
		}	  
	}

	/**
	 * Retreives all session id's from database
	 * @return arraylist with session id's
	 */
	public void processSessionIds()
	{
		//initialize connection
		Connection connection = initializeConnection();
		ArrayList<Integer> sessionList = new ArrayList<Integer>();
		try
		{			
			//create statement + set timeout
			Statement statement = connection.createStatement();
			statement.setQueryTimeout(30);	
			//get all sessionIDs
			ResultSet rs = statement.executeQuery("SELECT sessionID FROM session");
			while(rs.next())
			{
				sessionList.add(rs.getInt("sessionID"));				
			}
		}
		catch(SQLException e)
		{
			if (Debug.DEBUGGEN)
			{
				Debug.writeMessage("Error in connection database.getSessionIds", Level.WARNING, e);
			}
		}
		finally
		{
			try
			{
				mController.processSessionIds(sessionList);
				if(connection != null)
					connection.close();
			}
			catch(SQLException e)
			{
				if (Debug.DEBUGGEN)
				{
					Debug.writeMessage("Error in connection database.getSessionIDs FINALLY", Level.WARNING, e);
				}
			}
		}
	}

	/**
	 * Retrieves all data from specific session and returns this.
	 * @param sessionID which session to create replay data from
	 * @return arraylist with boatdatawrappers.
	 */
	public void processReplayData(int sessionID, int frequency)
	{
		//create new arraylist
		ArrayList<BoatDataWrapper> dataArray = new ArrayList<BoatDataWrapper>();
		//initialize connection
		Connection connection = initializeConnection();
		try
		{			
			//create statement + set timeout
			Statement statement = connection.createStatement();
			statement.setQueryTimeout(30);	
			//get all data rows for particular sessionID
			ResultSet rs = statement.executeQuery("SELECT * FROM data WHERE sessionID =" + sessionID);
			while(rs.next())
			{
				//create boatdatawrapper
				BoatDataWrapper boatdata = new BoatDataWrapper();
				//put row data in boatdatawrapper
				boatdata.setAllData(rs.getInt("angle"), 
						rs.getInt("sailPosition"),
						rs.getInt("rudderPosition"),
						rs.getInt("windDirection"),
						rs.getInt("motor"),
						rs.getInt("keel"),
						rs.getInt("controlMethod"),
						rs.getInt("RFID"),
						rs.getInt("speed"),
						rs.getLong("datetime"));
				//put wrapper in arraylist
				dataArray.add(boatdata);	
			}
		}
		catch(SQLException e)
		{
			if (Debug.DEBUGGEN)
			{
				Debug.writeMessage("Error in connection database.getReplayData", Level.WARNING, e);
			}
		}
		finally
		{
			try
			{
				if(connection != null)
					connection.close();
			}
			catch(SQLException e)
			{
				if (Debug.DEBUGGEN)
				{
					Debug.writeMessage("Error in connection database.getReplayData FINALLY", Level.WARNING, e);
				}
			}
		}
		mController.processReplayData(dataArray, frequency);

	}
}
